<?php
declare(strict_types = 1);

namespace app\index\service;

use think\admin\Service as AdminService;
//use PHPExcel_IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
//Xlsx类  保存文件功能类
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

use app\index\service\ConfigService;

class ExcelService extends AdminService
{

    //初始化配置
    protected $table = 'Excel';//表名

    // 初始化
    protected function initialize()
    {
        $this->config = config('excel');//调用配置文件示例
    }

    /**
    * excel操作
    * @author    saruri <saruri@163.com>
    * @date      2021/02/16 15:44:02
    * @auth true
    * @menu true
    * @login true
    * @throws \think\Exception
    * @throws \think\db\exception\DataNotFoundException
    * @throws \think\db\exception\DbException
    * @throws \think\db\exception\ModelNotFoundException
    */
    public function excelCtrl(array $arr)
    {
        //do somehing
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
        // 实例化 Spreadsheet 对象
        //$spreadsheet = new Spreadsheet();
        $filePath=public_path().$arr['pic'];
        $spreadsheet = $reader->load($filePath);
        # 获取活动工作薄
        $this->sheet = $spreadsheet->getActiveSheet();
        $cellC1 = $this->sheet->getCell('B2');
        $A1 = $this->sheet->getCell('A1');
        //echo '值: ', $cellC1->getValue(),PHP_EOL;
        //echo '坐标: ', $cellC1->getCoordinate(),PHP_EOL;
        $this->config['row'] = $this->sheet->getHighestRow();
        //获取总列数
        $this->config['column']=$this->sheet->getHighestColumn();
        $this->config['headLine']=$arr['headLine'];

        $arr['cell']=$cellC1->getValue();
        $arr['cellValuee']=$cellC1->getCoordinate();
        $arr['sheet']=$this->sheet ;
        $arr['row']=$this->config['row'] ;
        $arr['head']=$this->config['head'] ;
        $arr['column']=$this->config['column'];
        $this->result['param']=$arr;
        $this->result['title']=$arr['filename'];
        $this->result['md5']=$arr['md5'];
        $this->saveFilePath($arr['md5'], $filePath);
        $this->result['pages']=PageService::instance()->build($arr);
        return $this;
    }

    /**
    * 保存文件路径 md5 为索引
    * @author    saruri <saruri@163.com>
    * @date      2021/03/02 14:25:27
    * @auth true
    * @menu true
    * @login true
    * @throws \think\Exception
    * @throws \think\db\exception\DataNotFoundException
    * @throws \think\db\exception\DbException
    * @throws \think\db\exception\ModelNotFoundException
    */
    public function saveFilePath($md5, $path)
    {
        //do somehing
        $arr['md5']=$md5;
        $arr['path']=$path;
        ConfigService::instance()->saveMd5($arr);//保存md5 和 文件路径关系
        //exit("这里是:");
    }

    /**
    * 构建文件头部
    * @author    saruri <saruri@163.com>
    * @date      2021/02/16 17:52:15
    * @auth true
    * @menu true
    * @login true
    * @throws \think\Exception
    * @throws \think\db\exception\DataNotFoundException
    * @throws \think\db\exception\DbException
    * @throws \think\db\exception\ModelNotFoundException
    */
    public function excelHead()
    {
        //do somehing
        $this->result['head']=[];
        //数字
        if (intval($this->config['column'])>0) {
            $this->HeadNum();
        }
        $arr=[];
        $this->result['configHead']='';
        foreach ($this->config['head'] as $key => $value) {
            if ($value==$this->config['column']) {
                return $this;
            }

            
            $fieldName=$this->sheet->getCell($value.$this->config['headLine'])->getValue();
            if (is_string($fieldName) and $fieldName!='') {
                $this->result['head'][$key]['letter']=$value;
                $this->result['head'][$key]['val']=$fieldName;
                $this->result['head'][$key]['field']='';
                $this->result['configHead'].=$fieldName."|";
            }
        }
       
        //exit("这里是:");
        return $this;
    }

    /**
    * 方法说明
    * @author    saruri <saruri@163.com>
    * @date      2021/03/04 16:56:35
    * @auth true
    * @menu true
    * @login true
    */
    public function getExcelSql($msgBody)
    {
        $arr=json_decode($msgBody, true);
        //var_dump($arr);
        //$this->ws=$arr->server;
        $jsonMessage=[];
        //var_dump($this->ws);
        // $this->ws = app('think\swoole\Websocket');
        //$mqResult=$this->ws->to([1,2,3,4])->emit('testcallback', '队列消费了'.time());
        //$this->ws->push(1, "message: $jsonMessage");
        //exit("dec". $mqResult);
        $filePath=ConfigService::instance()->getFilePath($arr['md5']);
        
        $fieldArr=$this->getFieldNameArr($arr['configList']);
        $fieldNames=$this->buildFieldName($fieldArr);

        
        //$this->ws=$arr['server'];
        

        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
        // 实例化 Spreadsheet 对象
        //$spreadsheet = new Spreadsheet();
        //$filePath=public_path().$arr['pic'];
        $spreadsheet = $reader->load($filePath['path']);
        # 获取活动工作薄
        $this->sheet = $spreadsheet->getActiveSheet();
        
        // 综合考量 还是 单条插入合适
        //循环具体列
        $str='';
        $l='';
        $sql='';
        for ($i=$arr['page']['start']; $i <$arr['page']['end'] ; $i++) {
            # code...
            //构造字段列表
            $l=$this->makeFiled($fieldArr, $i);
            $sql.=$this->addSql($arr['tab'], $fieldNames, $l);
            //$sql.="INSERT INTO `".$arr['tab']."` (".$fieldNames.") value (".$l.")"."\r\n";
            $str.=$i.'-';
        }
          
        unset($reader);
        $reader=null;

        //$mqResult=$this->ws->to([1,2,3,4])->emit('testcallback', '队列消费了'.time());
        //$this->serv->push($frame->fd, "message: $jsonMessage");
        //$reader->Destroy();
        P($sql);
        return "处理成功SQL 语句是".$sql;
        unset($sql);
        $sql=null;
        return "处理成功SQL 语句是".$arr['md5'].'文件路径是'.$filePath['path']."处理结果是".$str."构造字段是".$l.'----字段名称是'.$fieldNames."\r\n\r\n";
        return "处理成功SQL 语句是".$msgBody;
    }
 
    /*
    *  @desc      拼接固定参数
    *  @author    saruri <saruri@163.com>
    *  @date      2021/03/08 00:20:34
    */
    public function addSql($tab, $field, $l)
    {
        //do somehing
        $str='';
        $arr=$this->addStaticField($tab, $field, $l);
        $str="INSERT INTO `".$arr['tab']."` (".$arr['field'].") value (".$arr['l'].");"."\r\n";
        return $str;
    }

    /*
    *  @desc      附加固定字段
    *  @author    saruri <saruri@163.com>
    *  @date      2021/03/08 00:26:40
    */
    public function addStaticField($tab, $field, $l)
    {
        //do somehing
        $arr=[];
        $arr['tab']=$tab;
        $arr['field']=$field.','.'typeid'.','.'channel';
        $arr['l']=$l.",'87','1'";
        // 生化试剂  85
        // 分析标准品 86
        // 液体试剂  87
        // 无机产品标准溶液 99
        //$arr['field']=$field.','.'post_author';
        //$arr['l']=$l.",1";
        return $arr;
    }



    /*
    *  @desc      构造插入字段名
    *  @author    saruri <saruri@163.com>
    *  @date      2021/03/04 19:23:32
    */
    public function buildFieldName($fieldArr)
    {
        //do somehing
        $str='';
        foreach ($fieldArr as $key => $value) {
            $str.=$value.",";
        }

        $str=trim($str, ",");
        return $str;
    }
   
    /**
    * 构造坐标
    * @author    saruri <saruri@163.com>
    * @date      2021/03/04 18:48:57
    */
    public function makeFiled($fieldArr, $i)
    {
        //do somehing
        $str='';
        $fieldStr='';
        foreach ($fieldArr as $key => $value) {
            //$str.=$key.$i."|";
            $cellC1 = $this->sheet->getCell($key.$i)->getValue();
            $cellC1=str_replace("'","''", $cellC1);
            $str.="'". $cellC1 ."',";
            $fieldStr.=",";
        }
        $str=trim($str, ",");
        return $str;
        exit("这里是:");
    }

    /**
    * 获取字段名字 数组
    * @author    saruri <saruri@163.com>
    * @date      2021/03/04 18:23:00
    * @auth true
    * @menu true
    * @login true
    * @throws \think\Exception
    * @throws \think\db\exception\DataNotFoundException
    * @throws \think\db\exception\DbException
    * @throws \think\db\exception\ModelNotFoundException
    */
    public function getFieldNameArr($list)
    {
        $configList=trim($list, ",");
        $fieldArr=explode(",", $configList);
        foreach ($fieldArr as $key => $value) {
            $arr=explode("|", $value); //$value);
            $name=str_replace("Field_", "", $arr[0]);
            if (isset($arr[1])) {
                $letter[$name]=$arr[1];
            }
            # code...
        }

        return $letter;
    }


    /**
    * 根据表头获取数据库里符合的配置数据
    * @author    saruri <saruri@163.com>
    * @date      2021/02/22 08:09:34
    * @auth true
    * @menu true
    * @login true
    * @throws \think\Exception
    * @throws \think\db\exception\DataNotFoundException
    * @throws \think\db\exception\DbException
    * @throws \think\db\exception\ModelNotFoundException
    */
    public function getHeadList()
    {
        $this->result['configList']=ConfigService::instance()->getList($this->result['configHead']);
        return $this;
    }


    /**
    * 返回结果
    * @author    saruri <saruri@163.com>
    * @date      2021/02/17 00:10:30
    * @auth true
    * @menu true
    * @login true
    * @throws \think\Exception
    * @throws \think\db\exception\DataNotFoundException
    * @throws \think\db\exception\DbException
    * @throws \think\db\exception\ModelNotFoundException
    */
    public function run()
    {
        //do somehing
        return $this->result;
    }
}
